package com.longway.core.ext.excel.util;

import java.util.Date;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.longway.core.ext.excel.data.ColumnMeta;
import com.longway.core.ext.excel.data.RowsetData;
import com.longway.core.ext.excel.data.TableDataset;
import com.longway.core.ext.excel.error.ErrorException;
import com.longway.core.ext.excel.formats.DefaultExcelFormats;
import com.longway.core.ext.excel.formats.ExcelFormats;

public class ExcelKit {
	
public static final String VERSION_2003 = "2003";
private static final int HEADER_ROW = 2;
private static final int MAX_ROWS = 65535;
private List<TableDataset> tableDatasets;
private String[] sheetNames = new String[]{"sheet"};
private int cellWidth = 20;//256*25+184
private Map<Integer, Integer> colwidths;
private int headerRow;
private String[] titles = null;
private String[][] headers;
private String[][] columns;
private Map<Integer,Integer>[] hbcolumns;
private Map<Integer,Integer>[] hjcolumns;
private Map<String,String>[] variables;
private ExcelFormats excelformats = new DefaultExcelFormats();
private String version;
private short contentHeight = 400;
private Map<String,CellStyle>[] cellstyles = null;
private Workbook wb = null;

		public ExcelKit(List<TableDataset> tableDatasets) {
		    this.tableDatasets = tableDatasets;
		}
		
		public static ExcelKit data(List<TableDataset> tableDatasets) {
		    return new ExcelKit(tableDatasets);
		}

    
		public Workbook export() {
			  //表头样式
	        //CellStyle headerStyle = getDefaultHeaderStyle(wb);
		    if (VERSION_2003.equals(version)) {
		    	   wb = new HSSFWorkbook();
		    	   //2003特殊处理
		    	   if(tableDatasets.size()>1){
		    		   for (int i = 0; i < tableDatasets.size(); i++){
		    			    TableDataset dataset = tableDatasets.get(i);
		                    if(dataset.count() > MAX_ROWS){
		                    	throw new IllegalArgumentException("Data [" + i + "] is invalid:invalid data size (" + dataset.count()  + ") outside allowable range (0..65535)");
		                    }
		                }
		    	   }else if (tableDatasets.size() == 1 && tableDatasets.get(0).count() > MAX_ROWS) {
		    		   TableDataset dataset  = tableDatasets.get(0);
		    		    tableDatasets = dataset.dice(dataset, MAX_ROWS);
		                String sheetName = sheetNames[0];
		                sheetNames = new String[tableDatasets.size()];
		                for (int i = 0; i < tableDatasets.size(); i++) {
		                    sheetNames[i] = sheetName + (i == 0 ? "" : (i + 1));
		                }
		                String[] header = headers[0];
		                headers = new String[tableDatasets.size()][];
		                for (int i = 0; i < tableDatasets.size(); i++) {
		                    headers[i] = header;
		                }
		                String[] column = columns[0];
		                columns = new String[tableDatasets.size()][];
		                for (int i = 0; i < tableDatasets.size(); i++) {
		                    columns[i] = column;
		                }
		            }
		    }else {
	            wb = new XSSFWorkbook();
	        }
		    if (tableDatasets.size() == 0) {
	            return wb;
	        }
		    for(int i = 0; i < tableDatasets.size(); i++) {
		    	 //创建sheet
		    	 Sheet sheet = wb.createSheet(sheetNames[i]);
		         Row row;
		         Cell cell;
		         int titleRow = 0;
		         TableDataset sheetdata = tableDatasets.get(i);
		         int cloumNum = sheetdata.getColumnCount();
		         //标题
		         if(titles != null){
		        	 row = sheet.createRow(titleRow);
		        	 row.setHeight((short)1000);
		        	 cell = row.createCell(0);
	                 cell.setCellValue(titles[i]);
	             	 CellRangeAddress region = new CellRangeAddress(titleRow, 0, titleRow,cloumNum);
			         sheet.addMergedRegion(region);
			         titleRow++;
		         }
		        
		         //表头
	             if (headers[i].length > 0) {
	                row = sheet.createRow(titleRow);
	                row.setHeight((short)500);
	                if (headerRow <= 0) {
	                    headerRow = HEADER_ROW;
	                }
	                headerRow = Math.min(headerRow, MAX_ROWS);
	                for (int h = 0, lenH = headers[i].length; h < lenH; h++) {
	                	 if(colwidths!=null&&colwidths.get(h)!=null){
	                     	sheet.setColumnWidth(h, 256*colwidths.get(h)+184);
	                     }else{
	                    	int colWidth = 256*cellWidth+184;
	                     	sheet.setColumnWidth(h, colWidth);
	                     	if(colWidth<255*256){
	                              sheet.setColumnWidth(h, colWidth < 3000 ? 3000 : colWidth);    
	                        }else{
	                              sheet.setColumnWidth(h,6000);
	                        }
	                     }
	                     cell = row.createCell(h);
	                     cell.setCellValue(headers[i][h]);
	                     excelformats.setHeader(wb,cell);
	                }
	             }
	             //渲染数据
	             int int_RowIndex = headerRow;
				 String tem_Value = "";
				 String str_Value = "&ryc&";
				 TableDataset clonedataset = null;
				 if(hbcolumns!=null){
					clonedataset = (TableDataset)sheetdata.clone();
				 }
	             for (int j = 0, len = sheetdata.count(); j < len; j++) {
	                 row = sheet.createRow(j + headerRow);
	                 row.setHeight(contentHeight);
	                 int_RowIndex = row.getRowNum();
	                 RowsetData rowdata = sheetdata.get(j);
	                 if (rowdata == null) {
	                     continue;
	                 }
	                 if (rowdata instanceof RowsetData) {
	                	 Map<Integer,Integer> hbmap = null;
	                	 Map<String,String> varia = null;
	                	 Map<String,CellStyle> css = null;
	                	 if(hbcolumns !=null){
	                		 hbmap = hbcolumns[i];
	                	 }
	                	 if(variables !=null){
	                		 varia = variables[i];
	                	 }
	                	 if(cellstyles != null){
	                		 css = cellstyles[i];
                		 }
	                	 
	                	 if(columns != null){
	                		 processAsRowsetDataByColumns(sheet,columns[i], row, rowdata,tem_Value,str_Value,hbmap,varia,clonedataset);
	                	 }else{
	                		 processAsRowsetData(sheet,row, rowdata,tem_Value,str_Value,hbmap,varia,clonedataset,css);
	                	 }
	                 } else {
	                     throw new RuntimeException("Not support type[" + rowdata.getClass() + "]");
	                 }
	             }
	             
	             int_RowIndex++;
	             //添加合计行
	             if(hjcolumns!=null) {
	            	 if(int_RowIndex >2){
	            		row = sheet.createRow(int_RowIndex);
	   	                row.setHeight((short)500);
	   	                cell = row.createCell(0, CellType.STRING);
	   	                cell.setCellValue("Total");
						for (int j = 1; j<cloumNum; j++) {
							cell = row.createCell(j);
							if(hjcolumns[i].get(j)!=null){
								 String HjCloumnName = (String)this.getHjColumn().get(hjcolumns[i].get(j));	
								 String hjformula = "SUM("+HjCloumnName+"3:"+HjCloumnName+""+(int_RowIndex)+")";
								 cell.setCellFormula(hjformula);
							}else{
								cell.setCellValue("");
							}
						}
	            	 }
				  }
				}
		    return wb;
		}
		
	  @SuppressWarnings("unchecked")
	  private  void processAsRowsetData(Sheet sheet,Row row, RowsetData rowdata ,String tem_Value,String str_Value,Map<Integer,Integer> hbcolumns,Map<String,String> variables,TableDataset clonedataset,Map<String,CellStyle> css){
	        Cell cell = null;
	        String columTypeName;
	        int int_RowIndex = row.getRowNum();
	        try {
		        for (int i = 0; i < rowdata.getColumnSize(); i++) {
		        	columTypeName = rowdata.getColumn(i).getDataTypeName();
				    if (columTypeName.equalsIgnoreCase("varchar")||columTypeName.equalsIgnoreCase("text"))
	                {   
				    	String cellValue = rowdata.getString(i);
				    	cellValue = variableReplace(variables,cellValue);
				    	cell = row.createCell(i, CellType.STRING);
						if(cellValue == null) {
                            cellValue="";
                        }
						cell.setCellValue(cellValue);
						tem_Value = cellValue;
						if(hbcolumns !=null && hbcolumns.get(i)!=null){
						   HbNumberCell(sheet,i,tem_Value,str_Value,int_RowIndex,clonedataset,rowdata,hbcolumns.get(i));				
					    }
	                }
				    else if (columTypeName.equalsIgnoreCase("bigint")||columTypeName.equalsIgnoreCase("int"))
	                {
				    	Double cellValue = rowdata.getDouble(i);
				    	cell = row.createCell(i, CellType.NUMERIC);
				    	cell.setCellValue(cellValue);
	                }
				    else if (columTypeName.equalsIgnoreCase("Date"))
	                {
				    	Date cellValue = rowdata.getDate(i);
				    	String strCellvalue = "";
				    	if(excelformats.getDateFormat() != null ){
				    		strCellvalue = excelformats.getDateFormat().format(cellValue);
				    	}
				    	cell = row.createCell(i, CellType.STRING);
				    	cell.setCellValue(strCellvalue);
	                }
				    else
	                {
				    	String cellValue = rowdata.getString(i);
				    	cellValue = variableReplace(variables,cellValue);
				    	cell = row.createCell(i, CellType.STRING);
						if(cellValue == null) {
                            cellValue="";
                        }
						cell.setCellValue(cellValue);
						tem_Value = cellValue;
						if(hbcolumns !=null && hbcolumns.get(i)!=null){
						   HbNumberCell(sheet,i,tem_Value,str_Value,int_RowIndex,clonedataset,rowdata,hbcolumns.get(i));				
					    }
	                }
                    if(css !=null){
                    	String keypattern = i+",*";
                    	CellStyle cspattern = css.get(keypattern);
                    	if(cspattern!=null){
                    		cell.setCellStyle(cspattern);
                    	}
                    	String key = i+","+int_RowIndex;
                    	CellStyle cskey = css.get(key);
                    	if(cskey != null){
                    		cell.setCellStyle(cskey);
                    	}
                    	
                    	if(cspattern == null && cskey == null){
                    		if(columTypeName.equalsIgnoreCase("VARCHAR2")||columTypeName.equalsIgnoreCase("CLOB")){
                    			excelformats.setStringCell(wb, cell);
                    		}
                    		else if (columTypeName.equalsIgnoreCase("NUMBER"))
          	                {
                    			excelformats.setNumberCell(wb, cell);
          	                }
                    		else if (columTypeName.equalsIgnoreCase("Date"))
        	                {
          	                	
        	                }
                    	}
                    }
				}
	        } catch (ErrorException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    }
	  
	  @SuppressWarnings("unchecked")
	  private  void processAsRowsetDataByColumns(Sheet sheet,String[] columns, Row row, RowsetData rowdata ,String tem_Value,String str_Value,Map<Integer,Integer> hbcolumns,Map<String,String> variables,TableDataset clonedataset){
	        Cell cell;
	        String columTypeName;
	        String columName;
	        int int_RowIndex = row.getRowNum();
	        try {
		        for (int i = 0; i < columns.length; i++) {
		        	columName = columns[i];
		        	ColumnMeta clm = rowdata.getColumn(columName);
		        	if(clm==null) {
                        continue;
                    }
		        	columTypeName = clm.getDataTypeName();
				    if (columTypeName.equalsIgnoreCase("varchar")||columTypeName.equalsIgnoreCase("text"))
	                {   
				    	String cellValue = rowdata.getFieldString(columName);
				    	cellValue = variableReplace(variables,cellValue);
				    	cell = row.createCell(i, CellType.STRING);
						if(cellValue == null) {
                            cellValue="";
                        }
						cell.setCellValue(cellValue);
						tem_Value = cellValue;
						if(hbcolumns !=null && hbcolumns.get(i)!=null){
						   HbNumberCell(sheet,i,tem_Value,str_Value,int_RowIndex,clonedataset,rowdata,hbcolumns.get(i));				
					    }
	                }
				    else if (columTypeName.equalsIgnoreCase("bigint"))
	                {
				    	Long cellValue = rowdata.getFieldLong(columName);
				    	cell = row.createCell(i, CellType.NUMERIC);
				    	cell.setCellValue(cellValue);
	                }
				    else if (columTypeName.equalsIgnoreCase("int"))
	                {
				    	Integer cellValue = rowdata.getFieldInteger(columName);
				    	cell = row.createCell(i, CellType.NUMERIC);
				    	cell.setCellValue(cellValue);
	                }
				    else if (columTypeName.equalsIgnoreCase("Date"))
	                {
				    	Date cellValue = rowdata.getFieldDate(columName);
				    	String strCellvalue = "";
				    	if(excelformats.getDateFormat() != null ){
				    		strCellvalue = excelformats.getDateFormat().format(cellValue);
				    	}
				    	cell = row.createCell(i, CellType.STRING);
				    	cell.setCellValue(strCellvalue);
	                }
				    else
	                {
				    	String cellValue = rowdata.getFieldString(columName);
				    	cellValue = variableReplace(variables,cellValue);
				    	cell = row.createCell(i, CellType.STRING);
						if(cellValue == null) {
                            cellValue="";
                        }
						cell.setCellValue(cellValue);
						tem_Value = cellValue;
						if(hbcolumns !=null && hbcolumns.get(i)!=null){
						   HbNumberCell(sheet,i,tem_Value,str_Value,int_RowIndex,clonedataset,rowdata,hbcolumns.get(i));				
					    }
	                }
				}
	        } catch (ErrorException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    }
	  
	  
		@SuppressWarnings("unchecked")
		public Map getHjColumn() {
			Map map = new Hashtable();
			map.put(0, "A");
			map.put(1, "B");
			map.put(2, "C");
			map.put(3, "D");
			map.put(4, "E");
			map.put(5, "F");
			map.put(6, "G");
			map.put(7, "H");
			map.put(8, "I");
			map.put(9, "J");
			map.put(10, "K");
			map.put(11, "L");
			map.put(12, "M");
			map.put(13, "N");
			map.put(14, "O");
			map.put(15, "P");
			return map;
		}	

	private static int getCount(TableDataset clonedataset,String str_Value,int w,int int_Start,String areaValue,int area){
			int lng_I = 0;
			RowsetData  rowsetdata = null;
			String dataTypeName="";
			String columnName="";
			for(int i=int_Start-2;i<clonedataset.count();i++){
				rowsetdata = clonedataset.get(i);
				dataTypeName = rowsetdata.getTableMeta().getColumns().get(w).getDataTypeName();
				try {
					if(dataTypeName.equals("NUMBER")) {columnName = String.valueOf(rowsetdata.getDouble(w)==null?"":rowsetdata.getDouble(w));}
					else {
                        columnName  = rowsetdata.getString(w)==null?"":rowsetdata.getString(w);
                    }
					if(str_Value.equals(columnName))
					{
							if(areaValue.equals(rowsetdata.getString(area))) {
                                lng_I++;
                            } else {
                                break;
                            }
					}else{
							break;
					}
				} catch (ErrorException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return lng_I;
		}
		
	private static void HbNumberCell(Sheet sheet,int i,String tem_Value,String str_Value,int int_RowIndex,TableDataset clonedataset,RowsetData rowsetdata,int area){
        	try {
				if(!str_Value.equals(tem_Value)){
				int lngHbRow = getCount(clonedataset,tem_Value,i,int_RowIndex,rowsetdata.getString(area),area);
				str_Value = tem_Value;
				CellRangeAddress region = new CellRangeAddress(i, int_RowIndex, i, int_RowIndex+lngHbRow-1);
		        sheet.addMergedRegion(region);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    }
	
	private static String variableReplace(Map<String,String> variable,String value){
		if(variable != null){
			for(String key : variable.keySet()){
				if(value.contains(key)){
					value.replaceAll(key, variable.get(key));
				}
			}
		}
		return value;
	}
    
	public ExcelFormats getExcelformats() {
		return excelformats;
	}
	public void setExcelformats(ExcelFormats excelformats) {
		this.excelformats = excelformats;
	}
	public ExcelKit version(String version) {
	    this.version = version;
	    return this;
	}
	
	public ExcelKit sheetNames(String... sheetName) {
	    this.sheetNames = sheetName;
	    return this;
	}
	
	public ExcelKit headerRow(int headerRow) {
	    this.headerRow = headerRow;
	    return this;
	}
	
	public ExcelKit headers(String[]... headers) {
	    this.headers = headers;
	    return this;
	}
	
	public ExcelKit columns(String[]... columns) {
	    this.columns = columns;
	    return this;
	}
	
	public ExcelKit cellWidth(int cellWidth) {
	    this.cellWidth = cellWidth;
	    return this;
	}
	
	public ExcelKit colwidths(Map<Integer, Integer> colwidths) {
	    this.colwidths = colwidths;
	    return this;
	}
	
	public ExcelKit cellstyles(Map<String,CellStyle>[] cellstyles) {
	    this.cellstyles = cellstyles;
	    return this;
	}
	
	
}
  
